import time

import PD.PdUtil
import PathUtil


# 定义一个函数来判断同一 ip 在十分钟内是否有不同的 pon
def has_different_pon(group):
    result = []
    for i, row in group.iterrows():
        time = row['故障发生时间']
        start_time = time - pd.Timedelta(minutes=10)
        end_time = time + pd.Timedelta(minutes=10)
        # 筛选出同一 ip 在十分钟时间窗口内的数据
        window = group[(group['故障发生时间'] >= start_time) & (group['故障发生时间'] < end_time)]
        # 判断 pon 是否有不同的值
        result.append(len(window[port_col].unique()) > 1)
    return pd.Series(result, index=group.index)


def has_different_olt(group):
    result = []
    for i, row in group.iterrows():
        time = row['故障发生时间']
        start_time = time - pd.Timedelta(minutes=10)
        end_time = time + pd.Timedelta(minutes=10)
        # 筛选出同一 ip 在十分钟时间窗口内的数据
        window = group[(group['故障发生时间'] >= start_time) & (group['故障发生时间'] < end_time)]
        # 判断 pon 是否有不同的值
        result.append(len(window[ip_col].unique()) > 1)
    return pd.Series(result, index=group.index)


if __name__ == '__main__':
    import pandas as pd

    ip_col = '传输设备ip'
    port_col = '传输设备端口2'

    file_paths = [
        r"D:\Download\WeChat Files\wxid_kdchbeq2xllp22\FileStorage\File\2025-06\1751277268498.xlsx"
    ]
    dfs = []
    for file_path in file_paths:
        df = pd.read_excel(
            file_path,
            usecols=['告警名称', '告警描述', '故障发生时间', ip_col, '传输设备端口']
        )
        dfs.append(df)
    df = pd.concat(dfs, ignore_index=True)
    df['故障发生时间'] = pd.to_datetime(df['故障发生时间'])
    # start_date = pd.to_datetime('2025-04-26')
    # end_date = pd.to_datetime('2025-05-16')
    # df = df[(df['故障发生时间'] >= start_date) & (df['故障发生时间'] <= end_date)]
    df = df[df['告警名称'] != '告警名称']
    df = df.drop_duplicates()

    t = pd.read_csv(PathUtil.olt_bras(), usecols=['OLT_IP', 'BRAS1_ip'])
    t.rename(columns={
        'OLT_IP': ip_col
    }, inplace=True)
    df = df.merge(t, on=ip_col, how='left')
    df['故障发生时间'] = pd.to_datetime(df['故障发生时间'], format='mixed')
    types = pd.read_csv(
        "D:\\家宽\\source\\t_rules_alarm_category.csv")

    types['告警名'] = types['告警名'].str.replace(r'\(\d+\)', '', regex=True)
    uplink = types[types['分段'] == '上联']['告警名']
    tuifu = types[types['类型'] == '板卡']['告警名']
    fenzhi = types[types['分段'] == '分支']['告警名']

    df[port_col] = df['传输设备端口'].apply(
        lambda x: '-'.join(str(x).split('-')[-4:]) if pd.notna(x) else "")

    df['BO间'] = df['告警名称'].isin(uplink) | df['告警描述'].str.contains('上联')
    df['分支'] = df['告警名称'].isin(fenzhi)
    df['单OLT'] = df['告警名称'].isin(tuifu)
    df['多PON口'] = df.groupby(ip_col)[[port_col, '故障发生时间']].apply(has_different_pon).reset_index(0,
                                                                                                        drop=True)

    df['单BRAS'] = df.groupby('BRAS1_ip')[[ip_col, '故障发生时间']].apply(has_different_olt).reset_index(0,
                                                                                                         drop=True)
    df['多PON口'] = df['多PON口'].astype(bool)
    df['单PON口'] = ~df['多PON口']
    # 新增一列传输设备端口2，提取传输设备端口根据 - 分割后的后4个部分
    filter = df[~(df['BO间'] | df['单OLT'])]
    print(f'''BO间: {df['BO间'].sum()}
单OLT: {df['单OLT'].sum()}
多PON口: {filter['多PON口'].sum()}
单PON口: {filter['单PON口'].sum()}

''')

    df['故障发生时间(分)'] = df['故障发生时间'].dt.floor('min')

    # 使用 .loc 筛选出符合条件的行
    mask = df['BO间']
    df['同bras同分钟oltip数'] = df[mask].groupby(['BRAS1_ip', '故障发生时间(分)'])[ip_col].transform(
        'nunique')

    df.to_excel('/temp/故障类别.xlsx', index=False)
